Imports System.Globalization
Imports NUnit.Framework
Imports pixolut.BizBlox
Imports System.Text.RegularExpressions

<TestFixture()> Public Class SQLBuilderTests
    Public Sub New()
        MyBase.New()
    End Sub

    <TestFixtureSetUp()> Public Sub GlobalInit()
        '        Setup.CreateDataSet()
    End Sub

    <TestFixtureTearDown()> Public Sub GlobalDestruct()
        '       Setup.ClearDataSet()
    End Sub

    Dim mySql As SQLBuilder
    Public Enum column
        columnA
        columnB
        columnC
    End Enum

    <SetUp()> Public Sub LocalInit()
        mySql = New SQLBuilder(New Student)
    End Sub

    <TearDown()> Public Sub LocalDestruct()
        mySql = Nothing
    End Sub

    <Test()> Public Sub testAddWhereNull()
        mySql.AddWhereNull(column.columnA)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test.columnA IS NULL") > -1)
    End Sub

    <Test()> Public Sub testAddWhere()
        mySql.addWhere(column.columnA, "a")
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test.columnA = 'a'") > -1)
    End Sub

    <Test()> Public Sub testAddWhereInt()
        mySql.addWhere(column.columnA, 99)
        Dim out As String = mySql.RenderSelect
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test.columnA = 99") > -1)
    End Sub

    <Test()> Public Sub testAddWhereNullFK()
        Dim sObj As New Student
        mySql.AddWhereNull(sObj, Student.Columns.StudentID)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("Student.StudentID IS NULL") > -1)
    End Sub

    <Test()> Public Sub testAddWhereMultiple()
        mySql.addWhere(column.columnA, "a")
        mySql.addWhere(column.columnA, "b")
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test.columnA = 'a'") > -1)
        Assert.IsTrue(out.IndexOf("test.columnA = 'b'") > -1)
    End Sub

    <Test()> Public Sub testAddWhereFK()
        Dim sObj As New Student
        mySql.addWhere(sObj, Student.Columns.StudentID, "a")
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("Student.StudentID = 'a'") > -1)
    End Sub

    <Test()> Public Sub testAddWhereFKBoolean()
        Dim sObj As New Student
        mySql.addWhere(sObj, Student.Columns.Age, True)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("Student.Age = '1'") > -1)
    End Sub

    <Test()> Public Sub testAddOrderByDescFK()
        Dim sObj As New Student
        mySql.addOrderByDesc(sObj, Student.Columns.Name)
        mySql.addOrderByDesc(column.columnA)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("ORDER BY ") > -1)
        Assert.IsTrue(out.IndexOf("Student.Name DESC") > -1)
        Assert.IsTrue(out.IndexOf("test.columnA DESC") > -1)
    End Sub

    <Test()> Public Sub testAddOrderByAscFK()
        Dim sObj As New Student
        mySql.addOrderBy(sObj, Student.Columns.Name)
        mySql.addOrderBy(column.columnA)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("ORDER BY ") > -1)
        Assert.IsTrue(out.IndexOf("Student.Name ASC") > -1)
        Assert.IsTrue(out.IndexOf("test.columnA ASC") > -1)
    End Sub

    <Test()> Public Sub testAddOrderByDesc()
        mySql.addOrderByDesc(column.columnA)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("ORDER BY ") > -1)
        Assert.IsTrue(out.IndexOf("test.columnA DESC") > -1)
    End Sub

    <Test()> Public Sub testAddOrderByAsc()
        mySql.addOrderBy(column.columnA)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("ORDER BY ") > -1)
        Assert.IsTrue(out.IndexOf("test.columnA ASC") > -1)
    End Sub

    <Test()> Public Sub testAddSearch()
        Dim sb As New SearchBuilder("x")
        mySql.addSearch(column.columnA, sb)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("(test.columnA LIKE '%x%')") > -1)
        Assert.IsTrue(out.IndexOf("(test.columnA LIKE '%x')") > -1)
        Assert.IsTrue(out.IndexOf("(test.columnA LIKE 'x%')") > -1)
        Assert.IsTrue(out.IndexOf("(test.columnA LIKE 'x')") > -1)
    End Sub

    <Test()> Public Sub testAddSearchConcatenatedColumns()
        Dim sb As New SearchBuilder("x")
        mySql.addSearchConcatenatedColumns(sb, " ", column.columnA, column.columnB)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("((test.columnA + ' ' + test.columnB) LIKE '%x%')") > -1)
        Assert.IsTrue(out.IndexOf("((test.columnA + ' ' + test.columnB) LIKE '%x')") > -1)
        Assert.IsTrue(out.IndexOf("((test.columnA + ' ' + test.columnB) LIKE 'x%')") > -1)
        Assert.IsTrue(out.IndexOf("((test.columnA + ' ' + test.columnB) LIKE 'x')") > -1)
    End Sub

    <Test()> Public Sub testAddOr()
        mySql.addWhere(column.columnA, "a")
        mySql.addOr()
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf(" OR ") > -1)
    End Sub

    <Test()> Public Sub testAddAnd()
        mySql.addWhere(column.columnA, "a")
        mySql.addAnd()
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf(" AND ") > -1)
    End Sub

    <Test()> Public Sub testAddAndFail()
        mySql.addAnd()
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsFalse(out.IndexOf(" AND ") > -1)
    End Sub

    <Test()> Public Sub testAddOrFail()
        mySql.addOr()
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsFalse(out.IndexOf(" OR ") > -1)
    End Sub

    <Test(), ExpectedException(GetType(InvalidOperationException))> Public Sub testParenUnbalancedFail()
        mySql.openParen()
        Dim out As String = mySql.render()
    End Sub

    <Test(), ExpectedException(GetType(InvalidOperationException))> Public Sub testNotParenUnbalancedFail()
        mySql.openNot()
        Dim out As String = mySql.render()
    End Sub

    <Test()> Public Sub testOpenCloseParen()
        mySql.openParen()
        mySql.closeParen()
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("(") > -1)
        Assert.IsTrue(out.IndexOf(")") > -1)
    End Sub

    <Test()> Public Sub testOpenCloseNot()
        mySql.openNot()
        mySql.closeNot()
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("(NOT(") > -1)
        Assert.IsTrue(out.IndexOf("))") > -1)
    End Sub

    <Test()> Public Sub testInnerJoin()
        Dim obj As New Student
        mySql.addInnerJoin(column.columnA, obj, Student.Columns.StudentID)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test INNER JOIN Student ON test.columnA=Student.StudentID") > -1)
    End Sub

    <Test()> Public Sub testSequentialInnerJoin()
        mySql = New SQLBuilder(New Student)
        Dim sobj As New Student
        Dim orderObj As New LunchOrder
        Dim fObj As New Food
        mySql.addInnerJoin(sobj, Student.Columns.StudentID, orderObj, LunchOrder.Columns.StudentID)
        mySql.addInnerJoin(orderObj, LunchOrder.Columns.FoodID, fObj, Food.Columns.FoodID)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("FROM Student INNER JOIN LunchOrder ON Student.StudentID=LunchOrder.StudentID INNER JOIN Food ON LunchOrder.FoodID=Food.FoodID") > -1)
    End Sub

    <Test()> Public Sub testLeftOuterJoin()
        Dim obj As New Student
        mySql.addLeftOuterJoin(column.columnA, obj, Student.Columns.StudentID)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test LEFT OUTER JOIN Student ON test.columnA=Student.StudentID") > -1)
    End Sub

    <Test()> Public Sub testSequentialLeftOuterJoin()
        mySql = New SQLBuilder(New Student)
        Dim sobj As New Student
        Dim orderObj As New LunchOrder
        Dim fObj As New Food
        mySql.addLeftOuterJoin(sobj, Student.Columns.StudentID, orderObj, LunchOrder.Columns.StudentID)
        mySql.addLeftOuterJoin(orderObj, LunchOrder.Columns.FoodID, fObj, Food.Columns.FoodID)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("FROM Student LEFT OUTER JOIN LunchOrder ON Student.StudentID=LunchOrder.StudentID LEFT OUTER JOIN Food ON LunchOrder.FoodID=Food.FoodID") > -1)
    End Sub

    <Test()> Public Sub testRightOuterJoin()
        Dim obj As New Student
        mySql.addRightOuterJoin(column.columnA, obj, Student.Columns.StudentID)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test RIGHT OUTER JOIN Student ON test.columnA=Student.StudentID") > -1)
    End Sub

    <Test()> Public Sub testSequentialRightOuterJoin()
        mySql = New SQLBuilder(New Student)
        Dim sobj As New Student
        Dim orderObj As New LunchOrder
        Dim fObj As New Food
        mySql.addRightOuterJoin(sobj, Student.Columns.StudentID, orderObj, LunchOrder.Columns.StudentID)
        mySql.addRightOuterJoin(orderObj, LunchOrder.Columns.FoodID, fObj, Food.Columns.FoodID)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("FROM Student RIGHT OUTER JOIN LunchOrder ON Student.StudentID=LunchOrder.StudentID RIGHT OUTER JOIN Food ON LunchOrder.FoodID=Food.FoodID") > -1)
    End Sub

    <Test()> Public Sub testFullOuterJoin()
        Dim obj As New Student
        mySql.addFullOuterJoin(column.columnA, obj, Student.Columns.StudentID)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test FULL OUTER JOIN Student ON test.columnA=Student.StudentID") > -1)
    End Sub

    <Test()> Public Sub testSequentialFullOuterJoin()
        mySql = New SQLBuilder(New Student)
        Dim sobj As New Student
        Dim orderObj As New LunchOrder
        Dim fObj As New Food
        mySql.addFullOuterJoin(sobj, Student.Columns.StudentID, orderObj, LunchOrder.Columns.StudentID)
        mySql.addFullOuterJoin(orderObj, LunchOrder.Columns.FoodID, fObj, Food.Columns.FoodID)
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("FROM Student FULL OUTER JOIN LunchOrder ON Student.StudentID=LunchOrder.StudentID FULL OUTER JOIN Food ON LunchOrder.FoodID=Food.FoodID") > -1)
    End Sub

    <Test()> Public Sub testWhereDate()
        mySql.AddWhereDateEQ(column.columnA, DateTime.ParseExact("2004-07-09 00:00:00Z", "u", CultureInfo.InvariantCulture.DateTimeFormat))
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test.columnA = '2004-07-09 00:00:00Z'") > -1)
    End Sub

    <Test()> Public Sub testWhereDateGT()
        mySql.AddWhereDateGT(column.columnA, DateTime.ParseExact("2004-07-09 00:00:00Z", "u", CultureInfo.InvariantCulture.DateTimeFormat))
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test.columnA > '2004-07-09 00:00:00Z'") > -1)
    End Sub

    <Test()> Public Sub testWhereDateGTE()
        mySql.AddWhereDateGTE(column.columnA, DateTime.ParseExact("2004-07-09 00:00:00Z", "u", CultureInfo.InvariantCulture.DateTimeFormat))
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test.columnA >= '2004-07-09 00:00:00Z'") > -1)
    End Sub

    <Test()> Public Sub testWhereDateLT()
        mySql.AddWhereDateLT(column.columnA, DateTime.ParseExact("2004-07-09 00:00:00Z", "u", CultureInfo.InvariantCulture.DateTimeFormat))
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test.columnA < '2004-07-09 00:00:00Z'") > -1)
    End Sub

    <Test()> Public Sub testWhereDateLTE()
        mySql.AddWhereDateLTE(column.columnA, DateTime.ParseExact("2004-07-09 00:00:00Z", "u", CultureInfo.InvariantCulture.DateTimeFormat))
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test.columnA <= '2004-07-09 00:00:00Z'") > -1)
    End Sub

    <Test()> Public Sub testAddDistinct()
        mySql.addDistinct()
        mySql.addSelect(column.columnA)
        mySql.addWhere(column.columnC, "hello")
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Dim pattern As String = "SELECT DISTINCT\s+test\.columnA"
        Assert.IsTrue(Regex.IsMatch(out, pattern))
    End Sub

    <Test()> Public Sub testComplexDistinctTop()
        mySql.addDistinct()
        mySql.setTop(100)
        mySql.addSelect(column.columnA)
        mySql.addSelect(column.columnB)
        mySql.addWhere(column.columnC, "hello")
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Dim pattern As String = "SELECT DISTINCT\s+TOP 100\s+test\.columnA,\s*test.columnB"
        Assert.IsTrue(Regex.IsMatch(out, pattern))
    End Sub


#Region "Comparison operators"

    <Test()> Public Sub testWhereGT()
        mySql.AddWhereGT(column.columnA, "25.20")
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test.columnA > '25.20'") > -1)
    End Sub

    <Test()> Public Sub testWhereGTForeign()
        mySql.AddWhereGT(New Student, Student.Columns.Age, "25")
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("Student.Age > '25'") > -1)
    End Sub

    <Test()> Public Sub testWhereGTE()
        mySql.AddWhereGTE(column.columnA, "25.20")
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test.columnA <= '25.20'") >= -1)
    End Sub

    <Test()> Public Sub testWhereGTEForeign()
        mySql.AddWhereGTE(New Student, Student.Columns.Age, "25")
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("Student.Age >= '25'") > -1)
    End Sub

    <Test()> Public Sub testWhereLT()
        mySql.AddWhereLT(column.columnA, "25.20")
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test.columnA < '25.20'") > -1)
    End Sub

    <Test()> Public Sub testWhereLTForeign()
        mySql.AddWhereLT(New Student, Student.Columns.Age, "25")
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("Student.Age < '25'") > -1)
    End Sub

    <Test()> Public Sub testWhereLTE()
        mySql.AddWhereLTE(column.columnA, "25.20")
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("test.columnA <= '25.20'") > -1)
    End Sub

    <Test()> Public Sub testWhereLTEForeign()
        mySql.AddWhereLTE(New Student, Student.Columns.Age, "25")
        Dim out As String = mySql.render()
        Console.WriteLine(out)
        Assert.IsTrue(out.IndexOf("Student.Age <= '25'") > -1)
    End Sub


#End Region

End Class
